﻿CREATE PROCEDURE [util].[SecurityGrantDbOwnership]
@login [sysname]
AS
BEGIN
	SET NOCOUNT ON

	DECLARE @RC int, @ERROR int
	SET @RC = 0

	DECLARE DB_CUR CURSOR FOR (
		SELECT [name]
		FROM master.dbo.sysdatabases
		WHERE ([dbid] > 4)
			AND([name] NOT IN ('DBADB','LiteSpeedLocal','QuestWorkDatabase'))
			AND(DATABASEPROPERTY([name],'IsReadOnly') = 0)
	)
	OPEN DB_CUR

	DECLARE @DbName sysname
	FETCH NEXT FROM DB_CUR INTO @DbName
	WHILE (@@FETCH_STATUS = 0)
		BEGIN
			PRINT @DbName

			DECLARE @user sysname, @query nvarchar(1000), @statement nvarchar(1000)
			SET @user = NULL
			SET @query = N'SELECT @user = db.[name] 
				FROM [' + @DbName + N'].dbo.sysusers AS db
				INNER JOIN master.dbo.syslogins AS ms
					ON (ms.[sid] = db.[sid])
					AND(ms.[name] = ''' + @login + N''')'
			EXEC dbo.sp_executesql @query, N'@user sysname OUTPUT', @user OUTPUT
			IF(@user IS NULL)
				BEGIN
					SET @user = @login
					SET @statement = N'EXEC [' + @DbName + N'].dbo.sp_adduser ''' + @login + ''''
					EXEC(@statement)
					SET @ERROR = @@ERROR
					IF(@ERROR <> 0) GOTO ERROR_HANDLER
				END

			SET @statement = N'EXEC [' + @DbName + N'].dbo.sp_addrolemember ''db_owner'', ''' + @user + ''''
			EXEC(@statement)
			SET @ERROR = @@ERROR
			IF(@ERROR <> 0) GOTO ERROR_HANDLER

			FETCH NEXT FROM DB_CUR INTO @DbName
		END
	CLOSE DB_CUR
	DEALLOCATE DB_CUR

	GOTO EXIT_PROC

ERROR_HANDLER:
	IF(@RC = 0)
		SET @RC = @ERROR

EXIT_PROC:
	RETURN(@RC)
END